from flask import g

from util.db_util import connect


# 插入新增宝宝
def insert_baby(name, nickname, age, image):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO baby (name, nickname, age,image,user_id) VALUES (%s, %s, %s, %s,%s)"
            cursor.execute(sql, (name, nickname, age, image, g.user['id']))
        conn.commit()
    finally:
        conn.close()


# 查询所有宝宝信息
def select_all_babies():
    conn = connect()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM baby where user_id=%s"
            cursor.execute(sql, (g.user['id']))
            result = cursor.fetchall()
            return result
    finally:
        conn.close()


# 查询所有宝宝名称
def select_all_baby_names():
    conn = connect()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT name,id,nickname FROM baby where user_id=%s"
            cursor.execute(sql, (g.user['id']))
            result = cursor.fetchall()
            return result
    finally:
        conn.close()


# 根据条件查询宝宝信息
def select_babies_by_condition(condition):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            sql = "SELECT * FROM baby WHERE id= " + condition
            cursor.execute(sql)
            result = cursor.fetchall()
            return result[0]
    finally:
        conn.close()


# 更新数据
def update_baby(id, name, age, nickname, image):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            if image is None:
                sql = "UPDATE baby SET name=%s, age=%s, nickname=%s WHERE id=%s"
                cursor.execute(sql, (name, age, nickname, id))
            else:
                sql = "UPDATE baby SET name=%s, age=%s, nickname=%s, image=%s WHERE id=%s"
                cursor.execute(sql, (name, age, nickname, image, id))
        conn.commit()
    finally:
        conn.close()


# 删除数据
def delete_baby(id):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            sql = "DELETE FROM baby WHERE id=%s"
            cursor.execute(sql, (id,))
        conn.commit()
    finally:
        conn.close()


# 测试示例
if __name__ == "__main__":
    # 插入数据
    # insert_baby('小黄', '男', '5个月', '["活泼", "调皮"]', '黄宝', '/static/image/黄宝.jpg')

    # 查询所有宝宝信息
    all_babies = select_all_babies()
    print("所有宝宝信息：", all_babies)

    # 根据条件查询宝宝信息
    condition = "sex='男'"
    male_babies = select_babies_by_condition(condition)
    print("性别为男的宝宝信息：", male_babies)

    # 更新数据
    # update_baby(1, '小黄黄', '男', '6个月', '["活泼", "调皮", "可爱"]', '黄黄宝', '/static/image/黄黄宝.jpg')

    # 删除数据
    # delete_baby(1)
